import re
import glob
import sqlite3
import asyncio
import psycopg2 as pg

from setting import *

def execute_script(sql_file: str):

    '''执行系统sqlite3的脚本文件'''
    try:
        conn = sqlite3.connect('ipfs.db')
        cursor = conn.cursor()
        f = open(sql_file)
        cursor.executescript(f.read())
    finally:
        cursor.close()
        conn.close()
        f.close()

class ExecuteSqlScript:
    '''
    def execute_script(self, sql_script):

        传入一个指定的sql脚本并执行
    
    def execute_all_script(self, script_path):

        执行所script_path文件下的sql脚本

    '''
    def __init__(self):

        self.dsn = f"host={PS_HOST} port={PS_PORT} user={PS_USER} password={PS_PASSWORD} dbname={PS_DATABASE}"

        self.creat_datebase()
        self.conn = pg.connect(self.dsn)
        self.cursor = self.conn.cursor()


    def creat_datebase(self):
        '''
        删除旧库、创建新数据库
        '''
        dsn = f"host={PS_HOST} port={PS_PORT} user={PS_USER} password={PS_PASSWORD}"
        conn = pg.connect(dsn)
        conn.autocommit = True
        cursor = conn.cursor()

        # drop_sql = f'DROP DATABASE IF EXISTS {PS_DATABASE};'
        # cursor.execute(drop_sql) 

        sql = f'CREATE DATABASE {PS_DATABASE} ENCODING "utf8";'
        try:
            cursor.execute(sql)
        except pg.errors.DuplicateDatabase as e:
            print(e)
        cursor.close()
        conn.close()

    def close(self):
        '''
        关闭游标和数据库
        '''
        self.cursor.close()
        self.conn.close()

    @staticmethod
    def clear_script(sql_script):
        '''
        清洗脚本--注释,生成sql语句
        '''
        with open(sql_script, encoding='utf-8') as f:
            
            sql_ = ''
            lines = f.readlines()
            for line in lines:
                if line.isspace():
                    continue
                elif line.startswith('--'):
                    continue
                else:
                    rep_str = re.search('--.*', line)
                    if rep_str:
                        line = line.replace(rep_str.group(),'')                 
                    sql_ += ' ' + line.strip()

        return sql_

    def execute_script(self, sql_script):
        '''
        执行脚本
        '''
        for sql in self.clear_script(sql_script).split(';'):
            if sql:
                self.cursor.execute(sql)
        self.conn.commit()


    def execute_all_script(self, script_path):

        '''
        获取script_path文件中所有sql脚本并执行
        '''

        try:

            scripts = glob.glob(f'{script_path}/*.sql')

            for script in scripts:

                self.execute_script(script)

        finally:
            self.close()



    
if __name__ == '__main__':

    pass
    





    